import pandas as pd
import sqlalchemy
from sqlalchemy import create_engine

eng = create_engine("mysql+pymysql://root:ydzhao@localhost:3306/python_mysql?charset=utf8")

df = pd.DataFrame([("海门",'121.15,31.89', 9 ),
    ("招远",'120.38,37.35', 10 ),
    ("舟山",'122.207216,29',10 ),
    ("盐城",'120.13,33.38', 10 ),
    ("赤峰",'118.87,42.28', 10 ),
    ("青岛",'120.33,36.07', 10 ),
    ("乳山",'121.52,36.89', 10 ),
    ("金昌",'102.188043,38',10 ),
    ("泉州",'118.58,24.93', 10 ),
    ("莱西",'120.53,36.86',109 ),
    ("日照",'119.46,35.42',109 ),
    ("胶南",'119.97,35.88', 39 ),
    ("南通",'121.05,32.08', 39 ),
    ("拉萨",'91.11,29.97',  39 ),
    ("云浮",'112.02,22.93', 39 ),
    ("梅州",'116.1,24.55',  39 ),
    ("文登",'122.05,37.2', 39 ),
    ("上海",'121.48,31.22', 12),
    ("威海",'122.1,37.5',   12),
    ("承德",'117.93,40.97', 12),
    ("厦门",'118.1,24.46',  12),
    ("汕尾",'115.375279,22',12),
    ("潮州",'116.63,23.68', 12),
    ("丹东",'124.37,40.13', 12),
    ("太仓",'121.1,31.45',  12),
    ("曲靖",'103.79,25.51', 12),
    ("烟台",'121.39,37.52', 12),
    ("福州",'119.3,26.08',  12),
    ("即墨",'120.45,36.38', 12),
    ("抚顺",'123.97,41.97', 12),
    ("玉溪",'102.52,24.35', 12),
    ("阳泉",'113.57,37.85', 12),
    ("莱州",'119.942327,37',12),
    ("湖州",'120.1,30.86',  12),
    ("汕头",'116.69,23.39', 12),
    ("昆山",'120.95,31.39', 12),
    ("宁波",'121.56,29.86', 12),
    ("湛江",'110.359377,21',12),
    ("揭阳",'116.35,23.55', 12),
    ("荣成",'122.41,37.16', 12),
    ("常熟",'120.74,31.64', 12),
    ("东莞",'113.75,23.04', 12),
    ("河源",'114.68,23.73', 12),
    ("淮安",'119.15,33.5',  12),
    ("泰州",'119.9,32.49',  12),
    ("南宁",'108.33,22.84', 12),
    ("营口",'122.18,40.65', 12),
    ("惠州",'114.4,23.09',  12),
    ("江阴",'120.26,31.91', 12),
    ("蓬莱",'120.75,37.8',  12),
    ("韶关",'113.62,24.84', 12),
    ("广州",'113.23,23.16', 12),
    ("延安",'109.47,36.6',  12),
    ("太原",'112.53,37.87', 12),
    ("清远",'113.01,23.7',  12),
    ("中山",'113.38,22.52', 12),
    ("昆明",'102.73,25.04', 12),
    ("寿光",'118.73,36.86', 12),
    ("盘锦",'122.070714,41',12),
    ("长治",'113.08,36.18', 12),
    ("深圳",'114.07,22.62', 12),
    ("珠海",'113.52,22.3',  12),
    ("宿迁",'118.3,33.96',  12),
    ("咸阳",'108.72,34.36', 12),
    ("铜川",'109.11,35.09', 12),
    ("平度",'119.97,36.77', 12),
    ("佛山",'113.11,23.05', 12),
    ("海口",'110.35,20.02', 12),
    ("江门",'113.06,22.61', 12),
    ("章丘",'117.53,36.72', 12),
    ("肇庆",'112.44,23.05', 12),
    ("大连",'121.62,38.92', 12),
    ("临汾",'111.5,36.08',  12),
    ("吴江",'120.63,31.16', 12),
    ("沈阳",'123.38,41.8',  12),
    ("苏州",'120.62,31.32', 12),
    ("茂名",'110.88,21.68', 12),
    ("嘉兴",'120.76,30.77', 12),
    ("长春",'125.35,43.88', 12),
    ("胶州",'120.03336,36', 12),
    ("银川",'106.27,38.47', 12),
    ("锦州",'121.15,41.13', 12),
    ("南昌",'115.89,28.68', 12),
    ("柳州",'109.4,24.33',  12),
    ("三亚",'109.511909,18',12),
    ("自贡",'104.778442,29',12),
    ("吉林",'126.57,43.87', 12),
    ("阳江",'111.95,21.85', 12),
    ("泸州",'105.39,28.91', 12),
    ("西宁",'101.74,36.56', 12),
    ("宜宾",'104.56,29.77', 12),
    ("成都",'104.06,30.67', 12),
    ("大同",'113.3,40.12',  12),
    ("镇江",'119.44,32.2',  12),
    ("桂林",'110.28,25.29', 12),
    ("宜兴",'119.82,31.36', 12),
    ("北海",'109.12,21.49', 12),
    ("西安",'108.95,34.27', 12),
    ("金坛",'119.56,31.74', 12),
    ("东营",'118.49,37.46', 12),
    ("遵义",'106.9,27.7',   12),
    ("绍兴",'120.58,30.01', 12),
    ("扬州",'119.42,32.39', 12),
    ("常州",'119.95,31.79', 12),
    ("潍坊",'119.1,36.62',  12),
    ("重庆",'106.54,29.59', 12),
    ("台州",'121.420757,28',12),
    ("南京",'118.78,32.04', 12),
    ("滨州",'118.03,37.36', 12),
    ("贵阳",'106.71,26.57', 12),
    ("无锡",'120.29,31.59', 12),
    ("本溪",'123.73,41.3',  12),
    ("渭南",'109.5,34.52',  12),
    ("宝鸡",'107.15,34.38', 12),
    ("焦作",'113.21,35.24', 12),
    ("句容",'119.16,31.95', 12),
    ("北京",'116.46,39.92', 12),
    ("徐州",'117.2,34.26',  12),
    ("衡水",'115.72,37.72', 12),
    ("包头",'110,40.58',    12),
    ("绵阳",'104.73,31.48', 12),
    ("枣庄",'117.57,34.86', 12),
    ("杭州",'120.19,30.26', 12),
    ("淄博",'118.05,36.78', 12),
    ("鞍山",'122.85,41.12', 12),
    ("溧阳",'119.48,31.43', 12),
    ("安阳",'114.35,36.1',  12),
    ("开封",'114.35,34.79', 12),
    ("济南",'117,36.65',    12),
    ("德阳",'104.37,31.13', 12),
    ("温州",'120.65,28.01', 12),
    ("九江",'115.97,29.71', 12),
    ("邯郸",'114.47,36.6',  12),
    ("临安",'119.72,30.23', 12),
    ("兰州",'103.73,36.03', 12),
    ("沧州",'116.83,38.33', 12),
    ("临沂",'118.35,35.05', 12),
    ("南充",'106.110698,30',12),
    ("天津",'117.2,39.13',  12),
    ("富阳",'119.95,30.07', 12),
    ("泰安",'117.13,36.18', 12),
    ("诸暨",'120.23,29.71', 12),
    ("郑州",'113.65,34.76', 12),
    ("聊城",'115.97,36.45', 12),
    ("芜湖",'118.38,31.33', 12),
    ("唐山",'118.02,39.63', 12),
    ("邢台",'114.48,37.05', 12),
    ("德州",'116.29,37.45', 12),
    ("济宁",'116.59,35.38', 12),
    ("荆州",'112.239741,30',12),
    ("宜昌",'111.3,30.7',   12),
    ("义乌",'120.06,29.32', 12),
    ("丽水",'119.92,28.45', 12),
    ("洛阳",'112.44,34.7',  12),
    ("株洲",'113.16,27.83', 12),
    ("莱芜",'117.67,36.19', 12),
    ("常德",'111.69,29.05', 12),
    ("保定",'115.48,38.85', 12),
    ("湘潭",'112.91,27.87', 12),
    ("金华",'119.64,29.12', 12),
    ("岳阳",'113.09,29.37', 12),
    ("长沙",'113,28.21',    12),
    ("衢州",'118.88,28.97', 12),
    ("廊坊",'116.7,39.53',   2),
    ("菏泽",'115.480656,35',21),
    ("合肥",'117.27,31.86',21 ),
    ("武汉",'114.31,30.52',21 ),
    ("大庆",'125.03,46.58',21 )],columns=['name','map','value'])
df.to_sql(
    name = 'geoCoordMap',
    con = eng,
    index = False,
    if_exists = 'replace',
    chunksize = 500,
    dtype = {
      'name':sqlalchemy.types.NVARCHAR(length=10),
      'map':sqlalchemy.types.NVARCHAR(length=128),
      'value':sqlalchemy.types.INTEGER
    }
)
